﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace VeryCodes.DBUtility
{
    public class PageQueryHelper
    {
        /// <summary>
        /// 获取分页查询SQL语句
        /// 查询全部字段
        /// </summary>
        /// <param name="currPage">当前页</param>
        /// <param name="pageSize">页码</param>
        /// <param name="strWhere">查询条件</param>
        /// <param name="sortExpression">排序字段</param>
        /// <param name="sorDirection">排序方向</param>
        /// <param name="tableName">表</param>
        /// <param name="defaultStr">用于分页字段</param>
        /// <returns></returns>
        public static string GetPageSQL(int currPage, int pageSize, string strWhere, string sortField, string sorDirection, string tableName, string pageField)
        {
            return GetPageSQL(currPage, pageSize, strWhere, sortField, sorDirection, tableName, pageField, "*");
        }

        /// <summary>
        /// 获取分页查询SQL语句
        /// </summary>
        /// <param name="currPage">当前页</param>
        /// <param name="pageSize">页码</param>
        /// <param name="strWhere">查询条件</param>
        /// <param name="sortExpression">排序字段</param>
        /// <param name="sorDirection">排序方向</param>
        /// <param name="tableName">表</param>
        /// <param name="defaultStr">用于分页字段</param>
        /// <param name="selectedcols">要查询的字段</param>
        /// <returns></returns>
        public static string GetPageSQL(int currPage, int pageSize, string strWhere, string sortField, string sorDirection, string tableName, string pageField, string selectCols)
        {
            //构造SQL查询语句
            StringBuilder strSQL = new StringBuilder();

            strSQL.Append("select");
            strSQL.Append(" top ");
            strSQL.Append(pageSize);
            strSQL.Append(" ");
            strSQL.Append(selectCols);
            strSQL.Append(" from ");
            strSQL.Append(tableName);

            //生成where条件
            string tempWhere = getStrWhere(currPage, pageSize, strWhere, sortField, sorDirection, tableName, pageField);

            //链接查询条件
            if (tempWhere != "")
            {
                strSQL.Append(" where ");
                strSQL.Append(tempWhere);
            }

            //链接排序条件
            if (sortField != string.Empty)//制定排序字段
            {
                strSQL.Append(" order by ");
                strSQL.Append(sortField);
                strSQL.Append(" ");
                strSQL.Append(sorDirection);
            }
            else//未指定排序字段
            {
                strSQL.Append(" order by ");
                strSQL.Append(pageField);
                strSQL.Append(" desc");
            }

            return strSQL.ToString();
        }

        /// <summary>
        /// 获取查询条件
        /// </summary>
        /// <param name="isPageIndexChanging"></param>
        /// <returns>string</returns>
        private static string getStrWhere(int currPage, int pageSize, string strWhere, string sortField, string sorDirection, string tableName, string pageField)
        {
            StringBuilder strWhereSB = new StringBuilder();

            if (currPage > 1)
            {
                if (strWhere.Trim() != "")
                {
                    strWhereSB.Append(strWhere);
                    strWhereSB.Append(" and ");
                }

                strWhereSB.Append(pageField);
                strWhereSB.Append(" not in (select top ");
                strWhereSB.Append((currPage - 1) * pageSize);
                strWhereSB.Append(" ");
                strWhereSB.Append(pageField);
                strWhereSB.Append(" from ");
                strWhereSB.Append(tableName);

                if (strWhere.Trim() != "")
                {
                    strWhereSB.Append(" where ");
                    strWhereSB.Append(strWhere);
                }

                if (sortField != string.Empty)
                {
                    strWhereSB.Append(" order by ");
                    strWhereSB.Append(sortField);
                    strWhereSB.Append(" ");
                    strWhereSB.Append(sorDirection);
                    strWhereSB.Append(")");
                }
                else
                {
                    strWhereSB.Append(" order by ");
                    strWhereSB.Append(pageField);
                    strWhereSB.Append(" desc)");
                }

                return strWhereSB.ToString();
            }

            return strWhere;
        }
    }
}